"""
Realtime statistics service for device metrics

Provides aggregated statistics from device_metrics data:
- Current values and latest readings
- Aggregations (min, max, avg, count) over time windows
- Multi-device and multi-metric statistics
"""

from __future__ import annotations

import logging
from datetime import datetime, timedelta
from typing import Any

from sqlalchemy import text
from sqlalchemy.orm import Session

logger = logging.getLogger(__name__)


class StatisticsService:
    """
    Realtime statistics service

    Provides aggregated statistics from device metrics without
    external dependencies - pure SQLite/SQL based analytics.
    """

    def __init__(self, session: Session) -> None:
        self.session = session

    def get_current_values(
        self,
        device_id: int | None = None,
        metric_names: list[str] | None = None,
    ) -> list[dict[str, Any]]:
        """
        Get current (latest) values for metrics

        Args:
            device_id: Filter by device ID (optional)
            metric_names: Filter by metric names (optional)

        Returns:
            List of latest metric values with timestamps
        """
        # Build query dynamically
        conditions = []
        params: dict[str, Any] = {}

        if device_id is not None:
            conditions.append("device_id = :device_id")
            params["device_id"] = device_id

        if metric_names:
            placeholders = ", ".join([f":metric_{i}" for i in range(len(metric_names))])
            conditions.append(f"metric_name IN ({placeholders})")
            for i, name in enumerate(metric_names):
                params[f"metric_{i}"] = name

        where_clause = "WHERE " + " AND ".join(conditions) if conditions else ""

        # Get latest value for each device+metric combination
        query = f"""
        WITH latest_metrics AS (
            SELECT
                device_id,
                metric_name,
                value,
                timestamp,
                quality,
                unit,
                ROW_NUMBER() OVER (PARTITION BY device_id, metric_name ORDER BY timestamp DESC) as rn
            FROM device_metrics
            {where_clause}
        )
        SELECT device_id, metric_name, value, timestamp, quality, unit
        FROM latest_metrics
        WHERE rn = 1
        ORDER BY device_id, metric_name
        """

        result = self.session.execute(text(query), params)
        rows = result.fetchall()

        return [
            {
                "device_id": row[0],
                "metric_name": row[1],
                "value": row[2],
                "timestamp": row[3],
                "quality": row[4],
                "unit": row[5],
            }
            for row in rows
        ]

    def get_aggregated_stats(
        self,
        device_id: int,
        metric_name: str,
        hours: int = 1,
    ) -> dict[str, Any]:
        """
        Get aggregated statistics for a metric over time window

        Args:
            device_id: Device ID
            metric_name: Metric name
            hours: Time window in hours (default: 1 hour)

        Returns:
            Aggregated statistics (min, max, avg, count, etc.)
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        query = """
        SELECT
            COUNT(*) as count,
            MIN(value) as min_value,
            MAX(value) as max_value,
            AVG(value) as avg_value,
            MIN(timestamp) as start_time,
            MAX(timestamp) as end_time
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        """

        result = self.session.execute(
            text(query),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        row = result.fetchone()

        if not row or row[0] == 0:
            return {
                "device_id": device_id,
                "metric_name": metric_name,
                "time_window_hours": hours,
                "count": 0,
                "min_value": None,
                "max_value": None,
                "avg_value": None,
                "start_time": None,
                "end_time": None,
            }

        return {
            "device_id": device_id,
            "metric_name": metric_name,
            "time_window_hours": hours,
            "count": row[0],
            "min_value": row[1],
            "max_value": row[2],
            "avg_value": row[3],
            "start_time": row[4],
            "end_time": row[5],
        }

    def get_multi_device_summary(
        self,
        device_ids: list[int] | None = None,
        hours: int = 24,
    ) -> list[dict[str, Any]]:
        """
        Get summary statistics for multiple devices

        Args:
            device_ids: List of device IDs (optional, None = all devices)
            hours: Time window in hours

        Returns:
            Summary statistics per device
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # Build device filter
        device_filter = ""
        params: dict[str, Any] = {"start_time": start_time}

        if device_ids:
            placeholders = ", ".join([f":device_{i}" for i in range(len(device_ids))])
            device_filter = f"AND device_id IN ({placeholders})"
            for i, dev_id in enumerate(device_ids):
                params[f"device_{i}"] = dev_id

        query = f"""
        SELECT
            device_id,
            COUNT(DISTINCT metric_name) as metric_count,
            COUNT(*) as total_readings,
            MIN(timestamp) as first_reading,
            MAX(timestamp) as last_reading
        FROM device_metrics
        WHERE timestamp >= :start_time
          {device_filter}
        GROUP BY device_id
        ORDER BY device_id
        """

        result = self.session.execute(text(query), params)
        rows = result.fetchall()

        return [
            {
                "device_id": row[0],
                "metric_count": row[1],
                "total_readings": row[2],
                "first_reading": row[3],
                "last_reading": row[4],
                "time_window_hours": hours,
            }
            for row in rows
        ]

    def get_metric_distribution(
        self,
        device_id: int,
        metric_name: str,
        hours: int = 24,
        bucket_count: int = 10,
    ) -> dict[str, Any]:
        """
        Get value distribution (histogram) for a metric

        Args:
            device_id: Device ID
            metric_name: Metric name
            hours: Time window in hours
            bucket_count: Number of histogram buckets

        Returns:
            Distribution statistics with histogram data
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # First get min/max to calculate bucket ranges
        range_query = """
        SELECT MIN(value), MAX(value)
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        """

        result = self.session.execute(
            text(range_query),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        row = result.fetchone()

        if not row or row[0] is None:
            return {
                "device_id": device_id,
                "metric_name": metric_name,
                "time_window_hours": hours,
                "min_value": None,
                "max_value": None,
                "buckets": [],
            }

        min_val, max_val = row[0], row[1]

        # Handle edge case where all values are the same
        if min_val == max_val:
            count_query = """
            SELECT COUNT(*)
            FROM device_metrics
            WHERE device_id = :device_id
              AND metric_name = :metric_name
              AND timestamp >= :start_time
            """
            count_result = self.session.execute(
                text(count_query),
                {
                    "device_id": device_id,
                    "metric_name": metric_name,
                    "start_time": start_time,
                },
            )
            count_row = count_result.fetchone()
            if not count_row:
                total_count = 0
            else:
                total_count = count_row[0]

            return {
                "device_id": device_id,
                "metric_name": metric_name,
                "time_window_hours": hours,
                "min_value": min_val,
                "max_value": max_val,
                "buckets": [
                    {
                        "range_start": min_val,
                        "range_end": max_val,
                        "count": total_count,
                    }
                ],
            }

        # Calculate bucket ranges
        bucket_size = (max_val - min_val) / bucket_count
        buckets = []

        for i in range(bucket_count):
            range_start = min_val + (i * bucket_size)
            range_end = min_val + ((i + 1) * bucket_size)

            # For last bucket, include upper bound
            if i == bucket_count - 1:
                bucket_query = """
                SELECT COUNT(*)
                FROM device_metrics
                WHERE device_id = :device_id
                  AND metric_name = :metric_name
                  AND timestamp >= :start_time
                  AND value >= :range_start
                  AND value <= :range_end
                """
            else:
                bucket_query = """
                SELECT COUNT(*)
                FROM device_metrics
                WHERE device_id = :device_id
                  AND metric_name = :metric_name
                  AND timestamp >= :start_time
                  AND value >= :range_start
                  AND value < :range_end
                """

            bucket_result = self.session.execute(
                text(bucket_query),
                {
                    "device_id": device_id,
                    "metric_name": metric_name,
                    "start_time": start_time,
                    "range_start": range_start,
                    "range_end": range_end,
                },
            )

            bucket_row = bucket_result.fetchone()
            count = bucket_row[0] if bucket_row else 0

            buckets.append(
                {
                    "range_start": round(range_start, 2),
                    "range_end": round(range_end, 2),
                    "count": count,
                }
            )

        return {
            "device_id": device_id,
            "metric_name": metric_name,
            "time_window_hours": hours,
            "min_value": min_val,
            "max_value": max_val,
            "buckets": buckets,
        }
